3-Computer Science-Software-Database

relational database

Databases have formatted records that allow indexing and searching. Databases {relational database} can have different-format records and relations among formats.

tables

Relational databases store data in two-dimensional tables. For example, table can be about people in a group. Rows are about one object or event, such as person. Columns are fields, properties, classes, or data categories, such as person names and weights.

tables: columns

Columns have value formats. Column values can be numbers, character strings, bitmaps, times, dates, or monetary amounts. Column values can have fixed or variable number of characters. Columns can be empty or require values. Column values can be unique or the same. Column values can be another-column values. Column values can be any sequence value, such as 1 to n.

tables: key column

Tables typically have main column {primary key}. For example, in Person table, primary key is person name or ID. Several columns can link to make primary key.

tables: index

Columns can have indexes. Indexes assign table-row number to column value. Columns can require that each value occur only once or allow values to repeat. For example, person names occur only once, but the same weight can apply to several people. It takes time to make indexes, but indexing speeds searches. Indexing is more efficient if table updates are infrequent.

tables: relations

Two tables cross-reference each other, if they both have same column. For example, Person table can have PersonID column and WeightCategory column. Weight table can have WeightCategory column and WeightAmount category. Both tables share WeightCategory column. Database queries that use both tables can yield WeightAmount for PersonID. WeightCategory column in weight table is reference {foreign key} for WeightCategory column in Person table. Database can allow only weight categories listed in weight table to be values for WeightCategory column in Person table.

trigger

Inserting, deleting, or updating table values can cause {trigger, database} actions on database tables. For example, if Person table lists all people and their groups and if group table lists group members, adding person and his or her group to Person table can automatically add person to Group table.

normal forms

Theory governs table structure in relational databases. Five principles {normal form, database} improve performance speeds and prevent update problems, data redundancies, and data inconsistencies. Tables and records typically have normal forms {normalization, database}, unless performance considerations require non-normalization.

normal forms: First Normal Form

Table records have same number of non-repeating fields. For example, people in Person table have names, weights, and heights, even if some people have no known height values.

normal forms: Second Normal Form

Tables have one primary key. In Person table, name field is primary key, because it identifies person uniquely. Other fields state facts about primary key, such as weight and height, and are not unique.

normal forms: Third Normal Form

Columns are independent of other columns. There is only one column about each property. For example, in Person table, there are not two columns about weight, one for kilograms and one for pounds, or one for weight numbers and one for weight categories. For weights, there can be a Weight table. Non-key fields cannot be subsets of, or have another relation to, primary keys. For example, Person table has name column and does not have nickname column. For nicknames, there can be a nickname table.

normal forms: Fourth Normal Form

Row and column cells have one value, not multiple values. In Person table, name column has only best name, not alternative names, and language column has only preferred language, not all languages spoken. For nicknames, there can be a nickname table. For languages, there can be a language table. In Person table, each column is only about one property, not multiple properties. For example, Person table does not have a name-language column. For names and languages, there can be a language table.

normal forms: Fifth Normal Form

If two columns relate, and relation does not have direction {symmetric constraint}, use several tables with as few fields as possible, rather than fewer tables with more fields. For example, to relate person, weight amount, and weight category, Person table has name and weight category columns, and weight table has weight amount and weight category. Person table does not have person, weight amount, and weight category columns. For asymmetric constraints, database must have two tables.

tablespace

Single files {tablespace} can store related tables. Tablespaces represent shared columns only once, so memory size and computer processes decrease and processing speed increases.

Tablespace tables {cluster} {data cluster} can share columns. Clusters place related information in one physical location and allow overlapping indexes. Clustering works well for adding data to tables but is not good for tables that have many deletions or updates.

pivoting of data

For tables, cell values in any column or row can become columns or rows {pivoting}|. The new table shows a different perspective on data, along a new dimension. Pivoting can correlate two table variables.

empty cells

Cells can be empty (NULL). Pivoting treats NULL the same as other cell values. Pivoting can make fewer cells and so fewer NULL cells.

example

Table has Days columns D1, D2, ... and Persons rows P1, P2, ... Table cells have Locations L1, L2, ..., with L1 in D1-P1 cell, and so on. See Figure 1. You can pivot table to make table with Days columns, Locations rows, and Persons in cells. See Figure 2. Cells fill by rule: Days and Locations have Persons. You can always pivot new table back to old table.

data rows

You can pivot table on one column to put another column in cells. For example, table can have Day, Location, People, Thing, and Amount columns. Row records are Days, Places, People, Things, and Amounts combinations. You can pivot on Day column and rows, to put Amount in cells. Now table rows are Thing, People, and Place combinations, and columns are Day values listed in original-table cells. Cell Amount matches Day, Place, People, and Thing. If new table has same or greater cell number as original table, you can pivot back to original table.

security policy

Databases need security {security policy}: database roles (USER and ADMINISTRATOR), role system privileges for each application, role object privileges for each application, users, and row-level security.

Application Security creates roles and grants them privileges for one application. Roles and privileges for other applications should not affect the application. Users should not be able to alter roles and privileges inside or outside of the application. Application security must include SQL*Plus, SQL*Loader, and all other such available applications which might allow unrestricted SQL statement execution.

Application Context sets up session-based attributes. Each user has attributes, such as a user name, a role, an application, tables and views authorized to access, and other information which can be used to determine context. The context is used to determine what is accessed in the session.

Access Control allows row-based security for tables and views using Oracle functions attached to the table or view which is the basis of an application. When a user enters a SELECT, INSERT, UPDATE, or DELETE statement on the table or view, Oracle modifies the statement to include a WHERE clause which limits the data affected.

A schema is owned by a user. Schemas are accessible to other users when privileges are granted by the owner. The USER is the schema which contains all objects.

triggering

Database change or command use {event, computer} can trigger action {triggering, database}, such as displaying report or executing program.